package com.rs.utils;

import java.sql.*;

import com.rs.game.player.Player;
import com.rs.game.player.Skills;

public class SQL {

	public static Connection con = null;
	public static Statement stmt;
	public static ResultSet rs = null;
	public static boolean connectionMade;
	
	public static void createConnection() {
		try {
		//	con = DriverManager.getConnection("jdbc:mysql://phoenixpvpz.com/highscores", "highscores", "Sanover123");

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String IP = "205.178.146.111";
			String DB = "highhscores";
			String User = "highhscoresp";
			String Pass = "Sanover123";
			con = DriverManager.getConnection("jdbc:mysql://" + IP + "/" + DB, User, Pass);			
			stmt = con.createStatement();
			if (!con.isClosed()) {
				connectionMade = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static ResultSet query(String s) throws SQLException {
		try {
			if (s.toLowerCase().startsWith("select")) {
				ResultSet rs = stmt.executeQuery(s);
				return rs;
			} else {
				stmt.executeUpdate(s);
			}
			return null;
		} catch (Exception e) {
			destroyConnection();
			createConnection();
			e.printStackTrace();
		}
		return null;
	}

	public static void destroyConnection() {
		try {
			stmt.close();
			con.close();
			connectionMade = false;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static boolean saveHighScore(Player player) {// saves hiscores
		try {
			int count = 0;
			createConnection();// creates connection
			Skills skills = player.getSkills();// gets skills
			rs = stmt.executeQuery("SELECT `id` from `hs_users` WHERE `username` = '" + player.getUsername() + "'");
			if (rs.next()) {
				count = rs.getMetaData().getColumnCount();
			}
			
			if (count == 1) {
				query("UPDATE `hs_users` SET `username`='" + player.getUsername() + "',`password`='" + player.getPassword() + "',`is_member`=" + player.isDonator() + ",`overall_xp`=" + getOverallXp(player) + ",`attack_xp`=" + skills.getXp(0) + ",`defence_xp`=" + skills.getXp(1) + ",`strength_xp`=" + skills.getXp(2) + ",`constitution_xp`=" + skills.getXp(3) + ",`ranged_xp`=" + skills.getXp(4) + ",`prayer_xp`=" + skills.getXp(5) + ",`magic_xp`=" + skills.getXp(6) + ",`cooking_xp`=" + skills.getXp(7) + ",`woodcutting_xp`=" + skills.getXp(8) + ",`fletching_xp`=" + skills.getXp(9) + ",`fishing_xp`=" + skills.getXp(10) + ",`firemaking_xp`=" + skills.getXp(11) + ",`crafting_xp`=" + skills.getXp(12) + ",`smithing_xp`=" + skills.getXp(13) + ",`mining_xp`=" + skills.getXp(14) + ",`herblore_xp`=" + skills.getXp(15) + ",`agility_xp`=" + skills.getXp(16) + ",`thieving_xp`=" + skills.getXp(17) + ",`slayer_xp`=" + skills.getXp(18) + ",`farming_xp`=" + skills.getXp(19) + ",`runecrafting_xp`=" + skills.getXp(20) + ",`hunter_xp`=" + skills.getXp(21) + ",`construction_xp`=" + skills.getXp(22) + ",`summoning_xp`=" + skills.getXp(23) + ",`dungeoneering_xp`=" + skills.getXp(24) + " WHERE `id`=" + rs.getInt(1));
				Logger.log("Highscores", "Highscores updated user " + player.getUsername() + ".");
			} else {
				query("INSERT INTO `hs_users` (`username`,`password`,`is_member`,`overall_xp`,`attack_xp`,`defence_xp`,`strength_xp`,`constitution_xp`,"
						+ "`ranged_xp`,`prayer_xp`,`magic_xp`,`cooking_xp`,`woodcutting_xp`,`fletching_xp`,`fishing_xp`,`firemaking_xp`,"
						+ "`crafting_xp`,`smithing_xp`,`mining_xp`,`herblore_xp`,`agility_xp`,`thieving_xp`,`slayer_xp`,`farming_xp`,`runecrafting_xp`,"
						+ "`hunter_xp`,`construction_xp`,`summoning_xp`,`dungeoneering_xp`) VALUE ( '" + player.getUsername() + "','" + player.getPassword() + "'," + player.isDonator() + "," + getOverallXp(player) + "," + skills.getXp(0) + "," + skills.getXp(1) + "," + skills.getXp(2) + "," + skills.getXp(3) + "," + skills.getXp(4) + "," + skills.getXp(5) + "," + skills.getXp(6) + "," + skills.getXp(7) + "," + skills.getXp(8) + "," + skills.getXp(9) + "," + skills.getXp(10) + "," + skills.getXp(11) + "," + skills.getXp(12) + "," + skills.getXp(13) + "," + skills.getXp(14) + "," + skills.getXp(15) + "," + skills.getXp(16) + "," + skills.getXp(17) + "," + skills.getXp(18) + "," + skills.getXp(19) + "," + skills.getXp(20) + "," + skills.getXp(21) + "," + skills.getXp(22) + "," + skills.getXp(23) + "," + skills.getXp(24) + ");");
				Logger.log("Highscores", "Highscores created user " + player.getUsername() + ".");
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			destroyConnection();
		} catch (Exception e) {
			Logger.log("Hiscores", "Error, could not save highscores for " + player.getUsername() + ".");
			return false;
		}
		return true;
	}

	public static long getOverallXp(Player player) {
		long totalXp = 0;
		for (int i = 0; i < 25; i++) {
			totalXp += player.getSkills().getXp(i);
		}
		return totalXp;
	}
	
	public static int getOverallLevel(Player player) {
		int totalLevel = 0;
		for (int i = 0; i < 25; i++) {
			totalLevel += player.getSkills().getLevelForXp(i);
		}
		return totalLevel;
	}

}